﻿using Knife.Core.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Knife.Core.DataBase
{
    public class CommonQuery
    {
        public static Page SelectPage(string tableName, string selectCols, string strWhere, string strOrder, int pageIndex, int pageSize, Dictionary<string, object> paramList = null)
        {
            Page page = new Page();
            List<Dictionary<string, object>> rows = SelectPageListCustom(tableName, selectCols, strWhere, strOrder, pageIndex, pageSize, paramList);
            int total = SelectCount(tableName, strWhere);
            page.rows = rows;
            page.total = total;
            return page;
        }
        public static List<Dictionary<string, object>> SelectPageListCustom(string tableName, string selectCols, string strWhere, string strOrder, int pageIndex = 1, int pageSize = 10, Dictionary<string, object> paramList = null)
        {
            string sql = string.Format(" select {0} from {1} ", selectCols, tableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                sql += string.Format(" where {0} ", strWhere);
            }
            if (!string.IsNullOrEmpty(strOrder))
            {
                sql += string.Format(" order by {0} ", strOrder);
            }
            sql += string.Format(" limit {0},{1}", (pageIndex - 1) * pageSize, pageSize);
            return DapperHelper.Query<Dictionary<string, object>>(sql, paramList);
        }
        public static List<T> SelectPageList<T>(string tableName, string selectCols, string strWhere, string strOrder, int pageIndex = 1, int pageSize = 10, Dictionary<string, object> paramList = null)
        {
            string sql = string.Format(" select {0} from {1} ", selectCols, tableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                sql += string.Format(" where {0} ", strWhere);
            }
            if (!string.IsNullOrEmpty(strOrder))
            {
                sql += string.Format(" order by {0} ", strOrder);
            }
            sql += string.Format(" limit {0},{1}", (pageIndex - 1) * pageSize, pageSize);
            return DapperHelper.Query<T>(sql, paramList);
        }
        public static List<Dictionary<string, object>> SelectListCustom(string tableName, string selectCols, string strWhere, string strOrder, Dictionary<string, object> paramList = null)
        {
            string sql = string.Format(" select {0} from {1} ", selectCols, tableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                sql += string.Format(" where {0} ", strWhere);
            }
            if (!string.IsNullOrEmpty(strOrder))
            {
                sql += string.Format(" order by {0} ", strOrder);
            }
            return DapperHelper.Query<Dictionary<string, object>>(sql, paramList);
        }
        public static List<T> SelectList<T>(string tableName, string selectCols, string strWhere, string strOrder, Dictionary<string, object> paramList = null)
        {
            string sql = string.Format(" select {0} from {1} ", selectCols, tableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                sql += string.Format(" where {0} ", strWhere);
            }
            if (!string.IsNullOrEmpty(strOrder))
            {
                sql += string.Format(" order by {0} ", strOrder);
            }
            return DapperHelper.Query<T>(sql, paramList);
        }
        public static int SelectCount(string tableName, string strWhere, Dictionary<string, object> paramList = null)
        {
            string sql = string.Format(" select count(1) from {0} ", tableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                sql += string.Format(" where {0} ", strWhere);
            }
            return DapperHelper.ExecuteScalar<int>(sql, paramList);
        }
        public static Dictionary<string, object> SelectOneCustom(string tableName, string selectCols, string strWhere, Dictionary<string, object> paramList = null)
        {
            string sql = string.Format(" select {0} from {1} ", selectCols, tableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                sql += string.Format(" where {0} ", strWhere);
            }
            return DapperHelper.QuerySingle<Dictionary<string, object>>(sql, paramList);
        }
        public static T SelectOne<T>(string tableName, string selectCols, string strWhere, Dictionary<string, object> paramList = null)
        {
            string sql = string.Format(" select {0} from {1} ", selectCols, tableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                sql += string.Format(" where {0} ", strWhere);
            }
            return DapperHelper.QuerySingle<T>(sql, paramList);
        }
        public static T SelectOne<T>(string tableName, string selectCols, string filedName, string fieldValue)
        {
            string sql = string.Format(" select {0} from {1} where {2}=@{2}", selectCols, tableName, filedName);
            Dictionary<string, object> paramList = new Dictionary<string, object>();
            paramList.Add(filedName, fieldValue);
            return DapperHelper.QuerySingle<T>(sql, paramList);
        }
        public static List<Dictionary<string, object>> SelectGroupListCustom(string tableName, string selectCols, string strWhere, string strOrder, string strGroup, Dictionary<string, object> paramList = null, string maxTableIDName = "id")
        {
            string sql = string.Format("select {0} from {1} where {2} in (select max({3}) from {4} where {5} group by {6})", selectCols, tableName, maxTableIDName, maxTableIDName, tableName, strWhere, strGroup);
            if (!string.IsNullOrEmpty(strOrder))
            {
                sql += string.Format(" order by {0} ", strOrder);
            }
            return DapperHelper.Query<Dictionary<string, object>>(sql, paramList);
        }
        public static List<T> SelectGroupList<T>(string tableName, string selectCols, string strWhere, string strOrder, string strGroup, Dictionary<string, object> paramList = null, string maxTableIDName = "id")
        {
            string sql = string.Format("select {0} from {1} where {2} in (select max({3}) from {4} where {5} group by {6})", selectCols, tableName, maxTableIDName, maxTableIDName, tableName, strWhere, strGroup);
            if (!string.IsNullOrEmpty(strOrder))
            {
                sql += string.Format(" order by {0} ", strOrder);
            }
            return DapperHelper.Query<T>(sql, paramList);
        }
        public static List<Dictionary<string, object>> SelectGroupPageListCustom(string tableName, string selectCols, string strWhere, string strOrder, string strGroup, int pageIndex = 1, int pageSize = 10, Dictionary<string, object> paramList = null, string maxTableIDName = "id")
        {
            tableName = string.Format("(select * from {0} where {1} in (select max({2}) from {3} where {4} group by {5})) aGr ", tableName, maxTableIDName, maxTableIDName, tableName, strWhere, strGroup);
            return SelectPageListCustom(tableName, selectCols, strWhere, strOrder, pageIndex, pageSize, paramList);
        }
        public static List<T> SelectGroupPageList<T>(string tableName, string selectCols, string strWhere, string strOrder, string strGroup, int pageIndex = 1, int pageSize = 10, Dictionary<string, object> paramList = null, string maxTableIDName = "id")
        {
            tableName = string.Format("(select * from {0} where {1} in (select max({2}) from {3} group by {4})) aGr ", tableName, maxTableIDName, maxTableIDName, tableName, strGroup);
            return SelectPageList<T>(tableName, selectCols, strWhere, strOrder, pageIndex, pageSize, paramList);
        }
        public static int SelectGroupCount(string tableName, string strWhere, string strGroup, Dictionary<string, object> paramList = null, string maxTableIDName = "id")
        {
            string sql = string.Format("select count(1) from {0} where {1} in (select max({2}) from {3} where {4} group by {5})", tableName, maxTableIDName, maxTableIDName, tableName, strWhere, strGroup);
            return DapperHelper.ExecuteScalar<int>(sql, paramList);
        }
    }
}
